#include "csqlite.h"
#include <windows.h>
#include <QString>



CSqlite::CSqlite(QString dbname, QString tbname)
{

    m_dbname = dbname;
    m_tbname = tbname;
    m_db = QSqlDatabase::addDatabase("QSQLITE");
    m_db.setDatabaseName(m_dbname);
    m_db.open();

}

bool CSqlite::doquery(QString sql)
{

    QSqlQuery query(m_db);
    auto ret =  query.exec(sql);
    if (!ret){
        qDebug() << query.lastError().text();
    }
    return ret;
}

bool CSqlite::addRecord(int id, QString name, QString gender)
{

    QSqlQuery query(m_db);
    query.prepare(QObject::tr("insert into %1 (id,name,gender) values(:id,:name,:gender);").arg(m_tbname));
    query.bindValue(":id",id);
    query.bindValue(":name",name);
    query.bindValue(":gender",gender);

    auto ret = query.exec();
    if (!ret){
        qDebug() << query.lastError().text();
    }
    return ret;
}

bool CSqlite::addRecord(QMap<QString, QString> row)
{
    QString strFields,strValues;

    //遍历row
    foreach (auto key, row.keys()) {
        strFields+=key;
        strFields+=",";
        strValues+= QString("\"%1\"").arg(row[key]); //字符串值要用双引号包裹
        strValues+=",";
    }

    //移除最后一个逗号
    strValues.remove(strValues.length()-1,1);
    strFields.remove(strFields.length()-1,1);

    //准备sql语句
    QString sql = QObject::tr("insert into %1 (%2) values(%3);").arg(m_tbname,strFields,strValues);
    qDebug() << sql.toUtf8().data();
    //添加记录
    auto ret = doquery(sql);

    return ret;

}



QJsonArray CSqlite::getRows(QString strCondition)
{
    //声明变量
    bool ret;
    QJsonArray res;

    //获取结果集
    QSqlQuery query(m_db);

    if (strCondition.isEmpty()){
        ret = query.exec(QObject::tr("select * from %1;").arg(m_tbname));
    }
    else
        ret = query.exec(QObject::tr("select * from %1 where %2;").arg(m_tbname,strCondition));


    if (!ret){

        qDebug() << query.lastError().text();
        return QJsonArray();

    }

    //获取字段列表
    auto fieldCount = query.record().count();
    QStringList fieldList ;
    for(int i=0;i<fieldCount;i++){

        fieldList.append(query.record().fieldName(i));
    }

    //遍历结果集 并打包成json数组
    while(query.next()){

        QJsonObject row;

        foreach (auto fieldName, fieldList) {
            row[fieldName] = query.value(fieldName).toString();
        }
        res.append(row);
    }

    return res;


}

